﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using InvestmentIntelligence.Data.Repository.EFImplementation;
using InvestmentIntelligence.DB;
using InvestmentIntelligence.DbModel.CustomModels;
using InvestmentIntelligence.DbModel.Models;

namespace InvestmentIntelligence.Data.Repository.SqlImplementation
{
    public class InvestmentSecuritySerieRepository : IIPRepository<InvestmentSecuritySerie>, IInvestmentSecuritySerieRepository
    {
        private const int MergeTimeout = 300;

        //public void Merge(IEnumerable<InvestmentSecuritySerie> items, MergeArea area)
        //{
        //    if (area.IdeaIds != null)
        //    {
        //        MergeOverIdeas(items, area.IdeaIds);
        //    }
        //    else
        //    {
        //        MergeOverFundBook(items, area.FundBookId);
        //    }
        //}

        //public void MergeOverFundBook(IEnumerable<InvestmentSecuritySerie> items, int fundBookId)
        //{
        //    const string targetTableSearchCondition = "i.FundBookEntityId = @FundBook";
        //    var parameters = new[]
        //                             {
        //                                 new KeyValuePair<string, object>("@FundBook", fundBookId)
        //                             };
        //    Merge(items, targetTableSearchCondition, parameters);
        //}

        //public void MergeOverIdeas(IEnumerable<InvestmentSecuritySerie> items, IEnumerable<Guid> ideaIds)
        //{
        //    var targetTableSearchCondition = String.Format("[IdeaId] IN ({0})", String.Join(",", ideaIds.Select(i => String.Format("'{0}'", i))));
        //    var parameters = new KeyValuePair<string, object>[0];
        //    Merge(items, targetTableSearchCondition, parameters);
        //}

        //private string CreateTempTable(DataConnection dc)
        //{
        //    var name = "##InvestmentSecuritySerie" + Guid.NewGuid().ToString().Replace("-", "");
        //    dc.Execute(string.Format("SELECT * INTO {0} FROM [Analytics].[InvestmentSecuritySerie] WHERE 1=2", name), CommandType.Text);
        //    return name;
        //}

        //private void Merge(IEnumerable<InvestmentSecuritySerie> items, string serchCondition, IEnumerable<KeyValuePair<string, object>> parameters)
        //{
        //    using(var dc = new DataConnection())
        //    {
        //        dc.Timeout = MergeTimeout;
        //        dc.Begin();

        //        var tableName = CreateTempTable(dc);
        //        dc.BulkCopy(tableName, items);
        //        ExecuteMerge(dc, tableName, serchCondition, parameters);

        //        dc.Commit();
        //    }
        //}

//        private void ExecuteMerge(DataConnection dc, string tempTable, string searchCondition, IEnumerable<KeyValuePair<string, object>> parameters)
//        {
//            const string mergePattern = @"
//WITH targetPnL AS (
//    SELECT 
//        pnl.* 
//    FROM
//        [Analytics].[InvestmentSecuritySerie] pnl  
//    LEFT JOIN
//        [Analytics].[Investment] i
//    ON
//        i.Id = pnl.InvestmentId
//    WHERE
//        {1}
//)
//MERGE
//	targetPnL pnl
//USING
//	{0} pnlt
//ON
//	pnl.[InvestmentId] = pnlt.[InvestmentId] AND pnl.[SecurityId] = pnlt.[SecurityId] AND pnl.[Date] = pnlt.[Date]
//WHEN MATCHED THEN 
//	UPDATE SET pnl.[AgeByTimeSeries] = pnlt.[AgeByTimeSeries], 
//               pnl.[MarketCapSum] = pnlt.[MarketCapSum],
//               pnl.[Weight] = pnlt.[Weight],
//               pnl.[Risk] = pnlt.[Risk],
//               pnl.[RoC] = pnlt.[RoC],
//               pnl.[CloseQuantity] = pnlt.[CloseQuantity],
//               pnl.[CloseExposure] = pnlt.[CloseExposure],
//               pnl.[PositionPnL] = pnlt.[PositionPnL],
//               pnl.[PositionFxPnL] = pnlt.[PositionFxPnL],
//               pnl.[TradePnL] = pnlt.[TradePnL],
//               pnl.[TradeFxPnL] = pnlt.[TradeFxPnL]
//WHEN NOT MATCHED THEN 
//	INSERT ([InvestmentId],[Date],[SecurityId],[AgeByTimeSeries],[MarketCapSum],[Weight],[Risk],[RoC],[CloseQuantity],[CloseExposure],[PositionPnL],[PositionFxPnL],[TradePnL],[TradeFxPnL])
//    VALUES ([InvestmentId],[Date],[SecurityId],[AgeByTimeSeries],[MarketCapSum],[Weight],[Risk],[RoC],[CloseQuantity],[CloseExposure],[PositionPnL],[PositionFxPnL],[TradePnL],[TradeFxPnL])
//WHEN NOT MATCHED BY SOURCE THEN
//	DELETE
//OPTION (MAXDOP 1)
//;
//";
//            var sqlCode = string.Format(mergePattern, tempTable, searchCondition);
//            foreach(var p in parameters)
//            {
//                dc.Parameters.AddWithValue(p.Key, p.Value);
//            }
//            dc.Execute(sqlCode, CommandType.Text);
//        }

        public IEnumerable<InvestmentSecuritySerie> GetFundBookState(int fundBookId)
        {
            const string sql = @"
WITH maxDates AS
(
	SELECT 
		pnl.SecurityId, MAX(pnl.[Date]) AS [Date]
	FROM 
		Analytics.Investment i
	INNER JOIN
		Analytics.InvestmentSecuritySerie pnl
	ON
		i.Id = pnl.InvestmentId
	WHERE 
		FundBookEntityId = @FundBookId
	GROUP BY
		pnl.SecurityId
)

SELECT
	pnl.*
FROM
	Analytics.InvestmentSecuritySerie pnl
INNER JOIN 
	Analytics.Investment i 
ON 
	i.Id = pnl.InvestmentId
INNER JOIN
	maxDates md
ON
	pnl.SecurityId = md.SecurityId AND pnl.[Date] = md.[Date]
WHERE i.FundBookEntityId = @FundBookId";

            using(var dc = new DataConnection())
            {
                dc.Parameters.AddWithValue("@FundBookId", fundBookId);
                return dc.Query<InvestmentSecuritySerie>(sql, CommandType.Text).ToArray();
            }
        }

        private static readonly List<InvestmentSecuritySerie> EmptySecuritySerie = new List<InvestmentSecuritySerie>(); 

        public IList<InvestmentSecuritySerie> GetInvestmentsSecuritySeries(IEnumerable<int> investmentIds)
        {
            if (!investmentIds.Any()) return EmptySecuritySerie;

            var comm = string.Format(@"
SELECT serie.*
FROM [Analytics].[InvestmentSecuritySerie] serie
WHERE InvestmentId IN ({0})
ORDER BY [Date]", string.Join(", ", investmentIds));

            using(var dc = new DataConnection())
            {
                return dc.Query<InvestmentSecuritySerie>(comm, CommandType.Text).ToList();
            }
        }

        public void Merge(IEnumerable<InvestmentSecuritySerie> items, IEnumerable<int> investments)
        {
            using (var dc = new DataConnection())
            {
                dc.Begin();

                var tempInvestmentsTable = CreateTempTable(dc, "InvestmentId");
                var tempSeriesTable = CreateTempTable(dc, "*");
                dc.BulkCopy(tempInvestmentsTable, investments.Select(i => new { InvestmentId = i }));
                dc.BulkCopy(tempSeriesTable, items);
                ExecuteMerge(dc, tempSeriesTable, tempInvestmentsTable);

                dc.Commit();
            }
        }

        private string CreateTempTable(DataConnection dc, string columns)
        {
            var tempTableName = "##InvestmentSecuritySerie" + Guid.NewGuid().ToString().Replace("-", "");
            dc.Execute(string.Format("SELECT {0} INTO {1} FROM [Analytics].[InvestmentSecuritySerie] WHERE 1=2", columns, tempTableName), CommandType.Text);
            return tempTableName;
        }

        private void ExecuteMerge(DataConnection dc, string tempSeriesTable, string tempInvestmentsTable)
        {
            const string mergePattern = @"
WITH targetSeries AS (
    SELECT 
        * 
    FROM
        [Analytics].[InvestmentSecuritySerie]
    WHERE
        InvestmentId IN (SELECT InvestmentId FROM {0})
)
MERGE
	targetSeries s
USING
	{1} st
ON
	s.[InvestmentId] = st.[InvestmentId] AND s.[SecurityId] = st.[SecurityId] AND s.[Date] = st.[Date]
WHEN MATCHED THEN 
	UPDATE SET s.[AgeByTimeSeries] = st.[AgeByTimeSeries], 
               s.[MarketCapSum] = st.[MarketCapSum],
               s.[Weight] = st.[Weight],
               s.[Risk] = st.[Risk],
               s.[RoC] = st.[RoC],
               s.[CloseQuantity] = st.[CloseQuantity],
               s.[CloseExposure] = st.[CloseExposure],
               s.[PositionPnL] = st.[PositionPnL],
               s.[PositionFxPnL] = st.[PositionFxPnL],
               s.[TradePnL] = st.[TradePnL],
               s.[TradeFxPnL] = st.[TradeFxPnL]
WHEN NOT MATCHED THEN 
	INSERT ([InvestmentId],[Date],[SecurityId],[AgeByTimeSeries],[MarketCapSum],[Weight],[Risk],[RoC],[CloseQuantity],[CloseExposure],[PositionPnL],[PositionFxPnL],[TradePnL],[TradeFxPnL])
    VALUES ([InvestmentId],[Date],[SecurityId],[AgeByTimeSeries],[MarketCapSum],[Weight],[Risk],[RoC],[CloseQuantity],[CloseExposure],[PositionPnL],[PositionFxPnL],[TradePnL],[TradeFxPnL])
WHEN NOT MATCHED BY SOURCE THEN
	DELETE
OPTION (MAXDOP 1)
;
";
            var sqlCode = string.Format(mergePattern, tempInvestmentsTable, tempSeriesTable);
            dc.Timeout = MergeTimeout;
            dc.Execute(sqlCode, CommandType.Text);
        }
    }
}
